*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
*	Creates a table reporting on differential attrition from the sample
*	----------------------------------------------------------------------------
*	IN: 	[analysis file]
*
*	OUT:	//name of output
			local outname 		"${city}_attrition.csv"
			//name of sheet in deck
			local sheetname 	"${city}_attrition"
*	----------------------------------------------------------------------------

	args fg lg fy ly sch_res ptype bw

*	settings

	local build 1

	//outcome for differential attrition regression
	local covs insample

	//pick VAM
	local olsvam OLSVAM_m_flag

*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

	cap log close
	log using "${log}`sheetname'.smcl", replace

*	build

	if `build' & "${city}"=="DEN"{
		use "${builddata}${city}_analysisfile_vam_`fg'_`lg'_`fy'_`ly'`sch_res'`bw'.dta", clear

		//prep enrollment for merge
		preserve
			use "${cleandata}enrollment_long", clear
			merge 1:1 stu year using "${cleandata}tests", gen(test_merge) keep(1 3) keepusing(test_grade math* ela*)

			//missing if grade of test is not grade currently enrolled
			foreach var of varlist math* ela*{
				replace `var' = . if test_grade != grade
			}

			//get baseline vars
			xtset stu year
			foreach x in sch math ela female black white hispanic asian fr_lunch sped {
				qui gen bl_`x' = L.`x'
				label var bl_`x' "Baseline `x'"
				if inlist("`x'","math","ela") qui gen bl_`x'_3 = L3.`x'
				if inlist("`x'","math","ela") label var bl_`x'_3 "Baseline `x' 3 yr lag"
			}

			foreach x in math ela female black white hispanic asian fr_lunch sped{
				keep if !mi(bl_`x')
			}
			gen year_app = year

			tempfile baselines
			save "`baselines'"
		restore

		//prep p-scores for merge
		preserve
			use "${cleandata}pscores", clear

			tostring app_sch, replace
			replace app_sch = "1" + app_sch if inrange(app_grade, 0, 5)
			replace app_sch = "6" + app_sch if inrange(app_grade, 6, 8)
			replace app_sch = "9" + app_sch if inrange(app_grade, 9, 12)
			destring app_sch, replace

			//basic sample restriction
			keep if inrange(year_app,`fy',`ly')
			keep if inrange(app_grade,`fg',`lg')

			keep stu app_sch year_app pscore_form pscore_freq offer

			reshape wide pscore_form pscore_freq offer, i(stu year_app) j(app_sch)

			rename *form* *form_*
			rename offer* offer_*

			merge 1:1 stu year_app using "`baselines'", gen(baseline_merge) keepusing(math)
			drop math
			keep if baseline_merge == 3

			tempfile wide_pscores
			save "`wide_pscores'"
		restore

		//merge in flattened pscores (is 1:1 for choice sample)
		drop pscore_merge
		merge m:1 stu year_app using "`wide_pscores'", gen(pscore_merge)

		qui mvencode pscore* offer*, mv(0) override
		//compress
		save "${builddata}${city}_analysisfile_full_`fg'_`lg'_`fy'_`ly'`sch_res'`bw'.dta", replace
	}
	if `build' & "${city}"=="NYC"{

		//prep enrollment for merge
		use "${cleandata}demo_info_08th_grade.dta", clear
		merge m:1 stu using "${cleandata}baseline_scores_standardized_grade8.dta", keep(1 3) gen(score_merge)
		keep if score_merge == 3

		foreach x in ss_math ss_ela female black white hispanic asian fr_lunch swd{
			keep if !mi(bl_`x')
		}
		gen year_app = year

		destring stu, replace

		tempfile baselines
		save "`baselines'"

		//prep p-scores
		forval year = `fy'/`ly'{
			use "${cleandata}school_level_pscores`bw'_`year'.dta", clear
			merge 1:1 stu using "${cleandata}runvar_dataset_program_pscore`bw'_`year'.dta", gen(rv_merge)
			drop quad*
			gen year_app = `year'
			tempfile pscores_`year'
			save "`pscores_`year''"
		}

		//append pscores
		clear
		set obs 1
		forval year = `fy'/`ly'{
			append using "`pscores_`year''"
		}
		drop in 1

		rename pscore_*_md2_* pscore_*2_*

		foreach var of varlist offer_* pscore_*{
			loc var_num = subinstr("`var'","B","1",.)
			loc var_num = subinstr("`var_num'","K","2",.)
			loc var_num = subinstr("`var_num'","M","3",.)
			loc var_num = subinstr("`var_num'","Q","4",.)
			loc var_num = subinstr("`var_num'","R","5",.)
			loc var_num = subinstr("`var_num'","X","6",.)

			rename `var' `var_num'
		}

		merge 1:1 stu year_app using "`baselines'", gen(baseline_merge) keepusing(bl_ss_math)
		drop bl_ss_math
		keep if baseline_merge == 3

		tempfile wide_pscores
		save "`wide_pscores'"

		use "${builddata}${city}_analysisfile_vam_`fg'_`lg'_`fy'_`ly'`sch_res'`bw'.dta", clear

		//merge in flattened pscores (is 1:1 for choice sample)
		merge m:1 stu year_app using "`wide_pscores'", gen(pscore_merge)

		//code missings to zero
		qui mvencode pscore* offer* rv*, mv(0) override

		//compress
		save "${builddata}${city}_analysisfile_full_`fg'_`lg'_`fy'_`ly'`sch_res'`bw'.dta", replace
	}
	if `build' & "${city}"=="NYCms"{

		//prep enrollment for merge
		use "${cleandata}demo_info_05th_grade.dta", clear
		merge m:1 stu using "${cleandata}baseline_scores_standardized_grade_ms_5.dta", keep(1 3) gen(score_merge)
		keep if score_merge == 3

		foreach x in ss_math ss_ela female black white hispanic asian fr_lunch swd ell{
			keep if !mi(bl_`x')
		}
		gen year_app = year

		destring stu, replace

		tempfile baselines
		save "`baselines'"

		//prep p-scores
		forval year = `fy'/`ly'{
			use "${cleandata}school_level_pscores`bw'_ms_`year'.dta", clear
			preserve
				u "${cleandata}runvar_dataset_program_pscore`bw'_ms_`year'.dta", clear
				destring stu, replace
				tempfile runvar
				sa `runvar'
			restore
			merge 1:1 stu using `runvar', gen(rv_merge)

			drop quad*
			gen year_app = `year'
			tempfile pscores_`year'
			save "`pscores_`year''"
		}

		//append pscores
		clear
		set obs 1
		forval year = `fy'/`ly'{
			append using "`pscores_`year''"
		}
		drop in 1

		rename pscore_*_md2_* pscore_*2_*

		foreach var of varlist offer_* pscore_*{
			loc var_num = subinstr("`var'","B","1",.)
			loc var_num = subinstr("`var_num'","K","2",.)
			loc var_num = subinstr("`var_num'","M","3",.)
			loc var_num = subinstr("`var_num'","Q","4",.)
			loc var_num = subinstr("`var_num'","R","5",.)
			loc var_num = subinstr("`var_num'","X","6",.)

			rename `var' `var_num'
		}

		merge 1:1 stu year_app using "`baselines'", gen(baseline_merge) keepusing(bl_ss_math)
		drop bl_ss_math
		keep if baseline_merge == 3

		tempfile wide_pscores
		save "`wide_pscores'"

		use "${builddata}${city}_analysisfile_vam_`fg'_`lg'_`fy'_`ly'`sch_res'`bw'.dta", clear

		//merge in flattened pscores (is 1:1 for choice sample)
		merge m:1 stu year_app using "`wide_pscores'", gen(pscore_merge)

		//code missings to zero
		qui mvencode pscore* offer* rv*, mv(0) override

		//compress
		save "${builddata}${city}_analysisfile_full_`fg'_`lg'_`fy'_`ly'`sch_res'`bw'.dta", replace
	}

*	load and clean

	use "${builddata}${city}_analysisfile_full_`fg'_`lg'_`fy'_`ly'`sch_res'`bw'.dta", clear

	* indicate year for those who have missing because not in analysis sample
	replace year = year_app + 1 if mi(year)

	* get risk sample again with merged in kids
	levelsof sch, local(allschools)
	sharpsample, lotteries(`allschools') ptype(`ptype') goodcell

	* any var used for any offer covariate
	gen any = 1

	* in analysis sample indicator
	gen insample = inlist(pscore_merge,1,3)

*	any offer pscores
	foreach sector in any {
		levelsof sch if `sector' == 1, local(`sector'_schools)
		gen pscore_`sector' = 0
		gen any_offer_`sector' = 0
		foreach sch in ``sector'_schools'{
			//pscores
			cap replace pscore_`sector' = pscore_`sector' + pscore_`ptype'_`sch'
		}
		replace pscore_`sector' = 1 if pscore_`sector' > 1
	}

*	set variables

	ivset, ptype(`ptype')
		local p = r(P)
		local S = r(S)
		local Z = r(Z)

*	offered VAM and expected VAM

	levelsof sch if lottery_`ptype'_sch & `S', local(lotteryschools)
	foreach m in vam {
		gen pscore_`m' = 0
		set varabbrev off
		foreach sch in `lotteryschools'{
			if "`m'" == "vam"  qui su b_`olsvam' if sch == `sch'
			replace pscore_`m' = pscore_`m' + pscore_`ptype'_`sch'*r(mean)
		}
		set varabbrev on
		qui egen ind_pscore_`m' = group(pscore_`m')

		//instrument (offered VAM)
		gen offered_`m' = 0
		foreach sch in `lotteryschools'{
			if "`m'" == "vam"  qui su b_`olsvam' if sch == `sch'
			replace offered_`m' = r(mean) if offer_`sch'
		}
	}

*	running variables

	local rv
	if strpos("`ptype'","2") local rv year##c.rv_*

*	indicate any offer

	cap drop offersum
	egen offersum = rowmax(offer_*)

*	get follow-up rate

	qui count if inlist(pscore_merge,1,3)
	local followed = r(N)
	qui count if inlist(pscore_merge,1,2,3)
	local total = r(N)

*	estimate attrition regressions

	local columns ""
	foreach model in con3 {
		foreach cov in `covs'{
			if "`model'"=="con3" eststo any_con3: qui reg `cov' offered_vam offersum pscore_vam pscore_any `rv' i.year if `S', r
			if "`model'"=="con2" eststo any_con2: qui reg `cov' offered_vam pscore_vam `rv' i.year if `S', r
			local columns `columns' any_`model'
		}
		estadd scalar followup = `followed' / `total'
	}



***	output

	esttab2 `columns' using "${tables}/raw/`outname'", replace ///
		se mtitles stats(N followup) nonumbers bfmt(j) se(j) sfmt(j) drop(rv_* *year *year* _cons, relax)
	estimates clear

	insheet using "${tables}/raw/`outname'", clear nonames
	export excel using "${tables}${city}_tabs_`c(username)'_`c(current_date)'.xlsx", sheetreplace sheet("`sheetname'")

	log close
